package CAT200;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;


public class Database_customer
{
    private static String dbURL = "jdbc:derby://localhost:1527/CAT200-DB;create=true;user=user;password=123";
    private static String tableName = "CUSTOMER";	// 
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args)
    {
        createConnection();

        createTable();
        insertData("2010-10-20","user1","abc001", "Tan Hui Xing" ,  "861001016244","013-78012000",  "AyerItam", "Penang","ferntan@yahoo.com","N");
        insertData("2010-10-20","user2","abc002", "Lee Heng Goh" ,  "891120086344","012-68012110",  "Taman Bahagia", "Johor","ferntan@yahoo.com","N");
        insertData("2010-10-21","ahCat","abc003", "Lai Wai Meng" ,  "890710146227","012-23812300",  "23, Jalan Gembira", "Kuala Lumpur","ferntan@yahoo.com","N");
        insertData("2010-10-21","ahmad","abc004", "Lim Hwan LiNG",  "900509086124","014-18212230",  "H35, Jalan Gemilang", "Johor","ferntan@yahoo.com","N");
        insertData("2010-10-21","ali"  ,"abc005", "Kelly Teoh"   ,  "900108016701","014-78080012",  "203, Tman Gembire", "Penang","ferntan@yahoo.com","N");
        
        displayData();
        shutdown();
    }


    //  to establish connection
    private static void createConnection()
    {
        try
        {
            //DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL);
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }



    // to create table
    private static void createTable()
    {
    	try
    	{
    		Statement stmt = conn.createStatement();
    		// Create table called my_table
    		String sql = "create table "+ tableName +
    					 "("+
 					 	    "CUST_DATEOFJOIN  DATE			,"		+
 					 	    "CUST_USERNAME 	  VARCHAR(20)	,"      +
    					 	"CUST_PASS 		  VARCHAR(15)	,"	    +
    					 	"CUST_NAME 		  CHAR(40)	    ,"	    +
    					 	"CUST_IC 		  VARCHAR(20)	,"		+
    					 	"CUST_TEL 	      VARCHAR(20)	,"		+
    					 	"CUST_CITY  	  VARCHAR(30)	,"		+
    					 	"CUST_STATE  	  VARCHAR(20)	,"		+
    					 	"CUST_EMAIL  	  VARCHAR(30)	,"		+
                            "BLACKLISTED      CHAR(1),"				+
    					 	"Primary Key(CUST_USERNAME)"+
    					 ")";

    		stmt.executeUpdate(sql);
    	}
    	catch (SQLException e)
    	{
    		System.out.println(e);
    	}

    }

    // to insert data
    private static void insertData(String CUST_DATEOFJOIN, String CUST_USERNAME, String CUST_PASS,String CUST_NAME,String CUST_IC,
    		String CUST_TEL,String CUST_CITY,String CUST_STATE,String CUST_EMAIL, String BLACKLISTED)
    {
        try
        {
            stmt = conn.createStatement();


            stmt.execute("insert into " + tableName +" values " +
            		     	"("	+
        		     		    "'"	+	CUST_DATEOFJOIN	+ 	  "',"  +
            		     		"'"	+	CUST_USERNAME	+ 	  "',"  +
            		     		"'"	+	CUST_PASS	    + 	  "',"  +
            		     		"'"	+	CUST_NAME		+ 	  "',"  +
            		     		"'"	+	CUST_IC			+ 	  "',"  +
            		     		"'"	+	CUST_TEL		+ 	  "',"  +
            		     		"'"	+	CUST_CITY		+ 	  "',"  +
            		     		"'"	+	CUST_STATE		+ 	  "',"  +
            		     		"'"	+	CUST_EMAIL		+ 	  "',"   +
            		     		"'" +   BLACKLISTED     +     "'"   +
            		     	")"
            		     );


            stmt.close();


        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }



    // to display the table
    
    private static void displayData()
    {
        try
        {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select * from " + tableName);
            ResultSetMetaData rsmd = results.getMetaData();
            int numberCols = rsmd.getColumnCount();
            for (int i=1; i<=numberCols; i++)
            {
                //print Column Names
                System.out.print(rsmd.getColumnLabel(i)+"\t\t");
            }

            System.out.println("\n-------------------------------------------------");

            while(results.next())
            {
                String id = results.getString(1);
                String name = results.getString(2);

                System.out.println(id + "\t\t" + name);
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }
    


    // to shut down the connection
    private static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }
        }
        catch (SQLException sqlExcept)
        {

        }

    }
}
